package com.dao;

import com.action.UserAction;

import com.bean.Record;
import com.bean.User;

import com.sql.SqlManagers;

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Types;

import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.OracleTypes;


public class UserDAO {
    public UserDAO() {
        super();
        // Tao 1 ket noi den database
        SqlManagers sm = new SqlManagers();
        con = sm.getCon();

    }

    private Connection con;


    public ArrayList<User> getAll() {
        ArrayList<User> list = new ArrayList<User>();

        try {
            //Su dung PreparedStatement de thuc hien query
            CallableStatement cstmt = null;
            cstmt = con.prepareCall("{call process_all_rows(?)}");
            cstmt.registerOutParameter(1, OracleTypes.CURSOR);
            cstmt.executeUpdate();
            ResultSet rs = (ResultSet)cstmt.getObject(1);
            while (rs.next()) {
                User ua = new User();
                ua.setId(rs.getInt("id"));
                ua.setUsername(rs.getString("username"));
                ua.setEmail(rs.getString("email"));
                ua.setGender(rs.getString("gender"));
                ua.setCountry(rs.getString("country"));
                list.add(ua);
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return list;
    }

    //Ham thuc thi xoa 1 ban ghi trong bang employees

    public boolean delete(int id) {
        boolean flag = false;
        try {
            //Su dung PreparedStatement de thuc hien query
            PreparedStatement ps;
            ps = con.prepareStatement("delete  from employees where id=?");

            ps.setInt(1, id);
            int n = ps.executeUpdate();
            if (n > 0)
                flag = true;


        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return flag;
    }
    //Ham thuc thi them 1 ban ghi trong bang employees

    public boolean insert(User u) {
        int n = 0;
        try {
            PreparedStatement ps;
            ps = con.prepareStatement("insert into employees values(ValueAutoIncre.nextval,?,?,?,?,?,?)");
            ps.setString(1, u.getUsername());
            ps.setString(2, u.getPassword());
            ps.setString(3, u.getEmail());
            ps.setString(4, u.getGender());
            ps.setString(5, u.getCountry());
            ps.setInt(6, u.getLevelemp());
            n = ps.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return n > 0;
    }
    //Ham kiem tra thon tin dang nhap

    public boolean checkLogin(User u) {
        //bien kiem tra
        boolean flag = false;
        try {
            PreparedStatement ps;
            ps = con.prepareStatement("select * from employees where username=? and password=?");

            ps.setString(1, u.getUsername().trim());
            ps.setString(2, u.getPassword().trim());
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                //Neu thon tin chinh xac bien kiem tra se dc gan bang true
                u.setLevelemp(rs.getInt("LevelEmp"));
                flag = true;
            }


        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return flag;
    }
    //Ham thuc thi hien thi 1 ban ghi trong bang employees

    public User getById(int id) {
        User ua = new User();
        try {
            PreparedStatement ps;
            ps = con.prepareStatement("select * from employees where id=?");
            //Tham so truyen vao se la id
            ps.setInt(1, id);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                ua.setId(id);
                ua.setUsername(rs.getString("Username"));
                ua.setPassword(rs.getString("Password"));
                ua.setEmail(rs.getString("Email"));
                ua.setGender(rs.getString("Gender"));
                ua.setCountry(rs.getString("Country"));
                ua.setLevelemp(rs.getInt("LevelEmp"));
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return ua;
    }


    public User getByUsername(String username) {
        User ua = new User();
        try {
            PreparedStatement ps;
            ps = con.prepareStatement("select * from employees where username=?");
            //Tham so truyen vao se la id
            ps.setString(1, username);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                ua.setId(rs.getInt("id"));
                ua.setUsername(rs.getString("Username"));
                ua.setPassword(rs.getString("Password"));
                ua.setEmail(rs.getString("Email"));
                ua.setGender(rs.getString("Gender"));
                ua.setCountry(rs.getString("Country"));
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return ua;
    }
    //Ham thuc thi hien thi cac ban ghi trong bang employees bang cach phan trang trong csdl

    public ArrayList<User> getAll(Record record) {
        ArrayList<User> list = new ArrayList<User>();
        ResultSet rs = null;
        CallableStatement cstmt = null;
        try {

            cstmt =
                    con.prepareCall("{call pagination(?,?,?,?,?)}", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            //currentPage la trang hien tai
            cstmt.setInt(1, record.getCurrentPage());
            // So ban ghi se hien thi tren 1 trang, o day mac dinh la 5
            cstmt.setInt(2, record.getRecordOnPage());
            // Dang ky tham so dau ra
            cstmt.registerOutParameter(3, OracleTypes.CURSOR);
            cstmt.registerOutParameter(4, Types.BIGINT);
            cstmt.registerOutParameter(5, Types.BIGINT);
            cstmt.executeUpdate();
            rs = (ResultSet)cstmt.getObject(3);
            record.setTotalRec(cstmt.getInt(4));
            record.setFirstRec(cstmt.getInt(5));
            while (rs.next()) {
                User ua = new User();
                ua.setId(rs.getInt("Id")); //Set o day co ma. van chua hien thi dc a:D, em pahi quay lai Action sua code da chu
                ua.setUsername(rs.getString("Username"));
                ua.setEmail(rs.getString("Email"));
                ua.setGender(rs.getString("Gender"));
                ua.setCountry(rs.getString("Country"));
                ua.setLevelemp(rs.getInt("LevelEmp"));

                list.add(ua);
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                // Dong ket noi
                if (rs != null) {
                    rs.close();
                }

                if (cstmt != null) {
                    cstmt.close();
                }
                if (con != null) {

                    con.close();
                }
            } catch (Exception e) {
                System.out.println(e.getClass().getName() + " line 59: " + e.toString());
            }
        }
        return list;
    }
    //Ham thuc thi update 1 ban ghi trong bang employees

    public void updateUser(User u) {
        try {
            PreparedStatement ps;
            ps =
 con.prepareStatement("update employees set Username=?, Email=?, Password=?, Gender=?, Country=?, LevelEmp=? where id=?");
            ps.setString(1, u.getUsername());
            ps.setString(2, u.getEmail());
            ps.setString(3, u.getPassword());
            ps.setString(4, u.getGender());
            ps.setString(5, u.getCountry());
            ps.setInt(6, u.getLevelemp());
            ps.setInt(7, u.getId());
            int n = ps.executeUpdate();

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}
